/**
 * 
 */
package com.wubo.common.data;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.wubo.common.jdbc.SQLExecutor;
import com.wubo.common.jdbc.SQLType;
import com.wubo.common.utils.NumberUtils;

/** 数据备份迁移管理
 * @author WuBo
 * @CreateDate 2010-9-10
 * @version 1.0.01
 */
public class DataBackupManager {
	private static String VERSION = "1.0";
	
	private SQLExecutor sqlExecutor;
	
	public DataBackupManager(SQLExecutor sqlExecutor){
		this.sqlExecutor = sqlExecutor;
	}
	
	public void backup(String[] tables, final OutputStream os) throws IOException {
		if(tables == null) throw new IllegalArgumentException("Argument tables cannot be null");
		String[] selects = new String[tables.length];
		
		for(int i=0; i<tables.length; i++){
			int index = tables[i].indexOf(",");
			if(index != -1){
				String spt= tables[i];
				tables[i] = spt.substring(0, index);
				selects[i] = spt.substring(index+1);
			}else{
				selects[i] = "select * from "+tables[i];
			}
		}
		backup(selects, tables, os, "UTF-8");
	}
	
	public void backup(String[] selects, String[] tables, final OutputStream os) throws IOException {
		if(selects == null) throw new IllegalArgumentException("Argument selects cannot be null");
		if(tables == null) throw new IllegalArgumentException("Argument tables cannot be null");
		backup(selects, tables, os, "UTF-8");
	}

	public void backup(final String[] selects, final String[] tables, final OutputStream os, final String encoding) throws IOException {
		if(selects == null) throw new IllegalArgumentException("Argument selects cannot be null");
		if(tables == null) throw new IllegalArgumentException("Argument tables cannot be null");
		if(selects.length != tables.length){
			throw new IllegalArgumentException("select's length muth equals tables's length");
		}
		if(os == null) throw new IllegalArgumentException("Argument os cannot be null");

		String encode = encoding == null ? "UTF-8" : encoding;

		final java.io.OutputStreamWriter osw = new java.io.OutputStreamWriter(os, encode);
		final java.io.BufferedWriter bw = new java.io.BufferedWriter(osw);
		try{
			if(tables != null){
				sqlExecutor.execute(new SQLExecutor.ConnectionCallback(){
					public Object execute(Connection conn) throws SQLException {
						java.sql.DatabaseMetaData dbmd = conn.getMetaData();
						
						printDatabaseMetaData(dbmd);
						
						//统计总数据行数
						long totalRecords = totalRecords(selects, conn);
						
						System.out.println("********** TotalRecords: "+totalRecords+" **********");
						
						//加上数据库元数据
						writeMetaData(tables, totalRecords, bw, dbmd);
						
						long index = 0;
						
						//当前进度
						int currentProcess = 0;
						
						for(int x=0; x<tables.length; x++){
							String table = tables[x];
							String sql = selects[x];
							
							//System.out.println("================== Table:"+table);
							//System.out.println("================== SQL:"+sql);
							
							Statement stmt = null;
							ResultSet rs = null;
							try{
								stmt = conn.createStatement();
								
								rs = stmt.executeQuery(sql);
	
								List<String> columnNames = new ArrayList<String>();
								
								java.util.Map<String, Integer> assigned = new java.util.HashMap<String, Integer>();
							
								String insert = createInsertString(rs, columnNames, table, assigned);
								
								while(rs.next()){
									Iterator<String> ite = columnNames.iterator();
									
									final List<String> params = new ArrayList<String>();
									
									final String values = createValueString(rs, assigned, ite, params);
									
									StringBuilder line = new StringBuilder(insert + values + "\n");
									
									if(params.size() > 0){
										for(int z=0; z<params.size(); z++){
											if(z>0)line.append("\n");
											line.append(params.get(z));
										}
									}
									
									bw.write(line.toString());
									bw.newLine();
									
									final int percent = (int) (NumberUtils.floor(++index / (totalRecords*1.0F), 2) * 100);
									
									if(percent > 0 && percent > currentProcess && percent % 5 == 0){
										currentProcess = percent;
										System.out.println("当前："+currentProcess+"%");
									}
								}
							}catch(IOException e){
								throw new RuntimeException(e);
							}finally{
								if(rs != null)rs.close();
								if(stmt != null)stmt.close();
							}
						}
						return null;
					}

					private String createValueString(ResultSet rs,
							java.util.Map<String, Integer> assigned,
							Iterator<String> ite,
							final List<String> params) throws SQLException {
						
						final StringBuilder values = new StringBuilder(" values(");
						
						int i = 0;
						while(ite.hasNext()){
							if(i>0){values.append(",");}
							
							String columnName = ite.next();
							Object value = rs.getObject(columnName);
							
							int type = getValueType(columnName, value, assigned);
							
							switch(type){
								case SQLType.NUMBER: values.append(value.toString()); break;
								case SQLType.STRING: {
									values.append("?");
									params.add("#STRING:<![CDATA[\n"+rs.getString(columnName).toString()+"\n]]>");
									break;
								}
								case SQLType.CHAR: {
									values.append("?");
									params.add("#STRING:<![CDATA[\n"+rs.getString(columnName).toString()+"\n]]>");
								}
								case SQLType.BOOLEAN: {
									values.append("?");
									params.add("#BOOLEAN:"+rs.getBoolean(columnName));
									break;
								}
								case SQLType.DATE: {
									values.append("?");
									params.add("#DATE:"+rs.getDate(columnName).getTime());
									break;
								}
								case SQLType.TIMESTAMP: {
									values.append("?");
									params.add("#DATETIME:"+rs.getTimestamp(columnName).getTime());
									break;
								}
								case SQLType.TIME: {
									values.append("?");
									params.add("#TIME:"+rs.getTime(columnName).getTime());
									break;
								}
								case SQLType.SHORT: {
									values.append(rs.getShort(columnName)); break;
								}
								case SQLType.INT: {
									values.append(rs.getInt(columnName)); break;
								}
								case SQLType.LONG: {
									values.append(rs.getLong(columnName)); break;
								}
								case SQLType.FLOAT: {
									values.append(rs.getFloat(columnName)); break;
								}
								case SQLType.DOUBLE: {
									values.append(rs.getDouble(columnName)); break;
								}
								case SQLType.DECIMAL: {
									values.append(value.toString()); break;
								}
								case -1: {
									values.append("?");
									params.add("#NULL");
									break;
								}
							}
							i ++;
						}
						return values.append(")").toString();
					}

					private String createInsertString(ResultSet rs,
							List<String> columnNames, String table,
							java.util.Map<String, Integer> assigned)
							throws SQLException {
						StringBuilder insert = new StringBuilder("insert into "+table+"(");
						
						ResultSetMetaData rsmd = rs.getMetaData();
						
						int columnCount = rsmd.getColumnCount();
						for(int i=0; i<columnCount; i++){
							String columnName = rsmd.getColumnName(i+1);
							int type = SQLType.getType(rsmd.getColumnTypeName(i+1), rsmd.getColumnDisplaySize(i+1));
							assigned.put(columnName, type);
							columnNames.add(columnName);
							/*System.out.println("Column:"+columnName+" - Type:"+rsmd.getColumnTypeName(i+1)
									+" - ClassType:"+rsmd.getColumnClassName(i+1)+" - IntType:"+rsmd.getColumnType(i+1)
									+" - Length:"+rsmd.getColumnDisplaySize(i+1));*/
						}
						if(columnNames.size() > 0){
							for(int z=0; z<columnNames.size(); z++){
								if(z>0)insert.append(",");
								insert.append(columnNames.get(z));
							}
						}
						insert.append(")");
						
						return insert.toString();
					}
					
					private int getValueType(String columnName, Object value, java.util.Map<String, Integer> assigned){
						if(value == null) return -1;
						//System.out.println("--------- ColumnName:"+columnName+" - Type:"+value.getClass().getName());
						if(assigned.containsKey(columnName)){
							return assigned.get(columnName);
						}
						return SQLType.getValueType(value);
					}
					
					private void printDatabaseMetaData(
							java.sql.DatabaseMetaData dbmd) throws SQLException {
						System.out.println("DatabaseProductName:"+dbmd.getDatabaseProductName());
						System.out.println("DatabaseProductVersion:"+dbmd.getDatabaseProductVersion());
						System.out.println("DatabaseDriverName:"+dbmd.getDriverName());
						System.out.println("DatabaseDriverVersion:"+dbmd.getDriverVersion());
					}

					private void writeMetaData(final String[] tables, final long totalRecords,
							final java.io.BufferedWriter bw,
							java.sql.DatabaseMetaData dbmd) throws SQLException {
						try{
							bw.write("@Version:"+VERSION);
							bw.newLine();
							bw.write("@Date:"+new java.util.Date().toString());
							bw.newLine();
							bw.write("@Author:WuBo");
							bw.newLine();
							bw.write("@DatabaseProductName:"+dbmd.getDatabaseProductName().replaceAll("\\r", " ").replaceAll("\\n", " "));
							bw.newLine();
							bw.write("@DatabaseProductVersion:"+dbmd.getDatabaseProductVersion().replaceAll("\\r", " ").replaceAll("\\n", " "));
							bw.newLine();
							bw.write("@DatabaseDriverName:"+dbmd.getDriverName().replaceAll("\\r", " ").replaceAll("\\n", " "));
							bw.newLine();
							bw.write("@DatabaseDriverVersion:"+dbmd.getDriverVersion().replaceAll("\\r", " ").replaceAll("\\n", " "));
							bw.newLine();
							bw.write("@Table:[");
							for(int x=0; x<tables.length; x++){
								if(x>0)bw.write(",");bw.write(tables[x]);
							}
							bw.write("]");
							bw.newLine();
							bw.write("@TotalRecords:"+totalRecords);
							bw.newLine();
						}catch(IOException e){
							throw new RuntimeException(e);
						}
					}
					
					private long totalRecords(String[] selects, Connection conn) throws SQLException {
						long result = 0;
						Statement stmt = null;
						ResultSet rs = null;
						try{
							for(String select : selects){
								stmt = conn.createStatement();
								rs = stmt.executeQuery("SELECT COUNT(*) FROM ("+select+") cr");
								if(rs.next()){
									result += rs.getLong(1);
								}
								rs.close();
								stmt.close();
							}
						}finally{
							if(rs != null)rs.close();
							if(stmt != null)stmt.close();
						}
						return result;
					}
				});
			}
		}finally{
			bw.close();
			osw.close();
			os.close();
		}
	}
	
	public static void showUsage(){
		System.out.println("USAGE：java DataBackupManager -s[ql]=SQL语句（多个SQL用冒号分隔）［可选］ -t[able]=表名（多个表名用冒号分隔） -f[ile]=导出文件名 -e[ncoding]=编码［可选，默认为UTF-8］");
	}
	
	public static void main(String[] args) throws Exception {
		if(args.length <2){
			showUsage();
			return;
		}
		String sqls = null;
		String tables = null;
		String file = null;
		String encoding = "UTF-8";
		for(int i=0; i<args.length; i++){
			String arg = args[i];
			if(arg.startsWith("-s=") || arg.startsWith("-sql=")){
				sqls = arg.split("=", 2)[1];
			}else if(arg.startsWith("-t=") || arg.startsWith("-table=")){
				tables = arg.split("=", 2)[1];
			}else if(arg.startsWith("-f=") || arg.startsWith("-file=")){
				file = arg.split("=", 2)[1];
			}else if(arg.startsWith("-e=") || arg.startsWith("-encoding=")){
				encoding = arg.split("=", 2)[1];
			}
		}
		if(tables == null || file == null){
			showUsage();
			return;
		}
		java.io.OutputStream os = new java.io.FileOutputStream(file);
		SQLExecutor exec = new SQLExecutor("db.properties");
		if(sqls != null){
			new DataBackupManager(exec).backup(sqls.split(":"), tables.split(":"), os, encoding);
		}else{
			new DataBackupManager(exec).backup(tables.split(":"), os);
		}
	}
}
